//This class is responsible for performing all the database operations
//required to create, find and manipulate Employee objects.
package employee.dao;
import java.sql.*;
import javax.swing.JTable;
import java.security.*;
import Password.*;
import employee.*;
import project.*;
import MainFunctions.MainFunctionsDAOS.*;

public class EmpManager
{
	private String input = null, type = null;
	Employee emp;
	Connection conn;
	private Table table = null;
	private JTable jTable = null;
	private String stringPass = null;
	static String UPDATE_EMP = "UPDATE employeeTable SET empId=?, "+
					"empPassword=?,idNumber=?,surname=?,name=?,"+
					"initials=?, title=?, address1=?,"+
					"address2=?, address3=?, address4=?, phone1=?,"+
					"phone2=?, email=?, birthDate=?, joinDate=?,"+
					"accessLevel=? WHERE empId = ?";
	static String QUERY_EMP_ID = "SELECT empId, empPassword, idNumber,"+ 
					"surname, name, initials, title, address1,"+ 
					"address2, address3, address4, phone1, phone2,"+ 
					"email, birthDate, joinDate, accessLevel "+
					"FROM employeeTable "+ 
					"WHERE empId LIKE ?";
	static String QUERY_NAME = 	"SELECT empId, empPassword, idNumber,"+ 
					"surname, name, initials, title, address1,"+ 
					"address2, address3, address4, phone1, phone2,"+ 
					"email, birthDate, joinDate, accessLevel "+
					"FROM employeeTable "+ 
					"WHERE surname LIKE ?";
	static String QUERY_PHONE = "SELECT empId, empPassword, idNumber,"+ 
					"surname, name, initials, title, address1,"+ 
					"address2, address3, address4, phone1, phone2,"+ 
					"email, birthDate, joinDate, accessLevel "+
					"FROM employeeTable "+ 
					"WHERE phone1 LIKE ?";
	public static String GET_EMP_BY_SURNAME = 
					"SELECT empId, name, surname, accessLevel, phone1" +
					" FROM employeeTable WHERE surname LIKE ?";
	public static String GET_EMP_BY_PHONE = 
					"SELECT empId, name, surname, accessLevel, phone1" +
					" FROM employeeTable WHERE phone1 LIKE ? "+
					"OR phone2 LIKE ?";
	
	
	public EmpManager(Connection conn)
	{
		try
		{
			String userid, password;
			userid = "root";
			password = "";
			//MySQL_Connection msc = new MySQL_Connection(
			//		"turtleData","localhost",userid, password);
			//conn = msc.getMySQLConnection();
			this.conn = conn;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}
	//This method is used when an Employee is first generated by the GUI,
	//it's only responsibility is to create a new record in the DB.
	public void commitEmp(Employee emp)
	{
		try
		{
			this.emp = emp;
			String [] str = emp.getEmpAttrString();
			Statement stmt = conn.createStatement();
			String insert;
			
			//This converts the access level from the name used 
			//in the GUI to the number used to represent the relevant
			//level in the DB.
			if(str[16].equals("Assistant"))
			{
				str[16] = "1";
			}
			if(str[16].equals("Stock Controller"))
			{
				str[16] = "2";
			}
			if(str[16].equals("Manager"))
			{
				str[16] = "3";
			}
			
			insert = "INSERT INTO employeeTable VALUES('"+
				str[0]+"','"+str[1]+"','"+str[2]+"','"+str[3]+"','"+
				str[4]+"','"+str[5]+"','"+str[6]+"','"+str[7]+"','"+
				str[8]+"','"+str[9]+"','"+str[10]+"','"+str[11]+"','"+
				str[12]+"','"+str[13]+"','"+str[14]+"','"+
				str[15]+"','"+str[16]+"')";
			int rows = stmt.executeUpdate(insert);
			System.out.println("Rows changed: "+rows);
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
	}
	//This method searches the db for multiple records matching a 
	//given input and returns a JTable for LookEmpPanel to graphically
	//display to a user.
	public JTable searchEmp(String inputString, String critString)
		{
			String input = inputString;
			String criteria = critString;
			try
			{
				if(criteria.equals("Last Name"))
				{
					ResultSet rs;
					PreparedStatement ps = conn.prepareStatement(
						GET_EMP_BY_SURNAME,
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
					ps.setString(1, input +'%');
					rs = ps.executeQuery();
					table = new Table();
					jTable = table.createStockSearchTable(rs);
				}
				else
				{
					ResultSet rs;
					PreparedStatement ps = conn.prepareStatement(
						GET_EMP_BY_PHONE,
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
					ps.setString(1, input +'%');
					ps.setString(2, input +'%');
					rs = ps.executeQuery();
					table = new Table();
					jTable = table.createStockSearchTable(rs);
				}
			}
			catch(SQLException sqle)
			{
				System.out.println(sqle.getMessage());
			}
			return jTable;
		}
	//This method is another search method used when there can only be one
	//record returned, such as in the case of searching by an ID.
	public Employee findEmp(String inputString, String typeString)
	{
		String input = inputString;
		String type = typeString;
		try
		{		
			PreparedStatement pstmt;
			ResultSet rs;
			if(type.equals("Employee ID"))
			{
				pstmt = conn.prepareStatement(
						QUERY_EMP_ID);
				System.out.println("Employee ID");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			else if(type.equals("Last Name"))
			{
				pstmt = conn.prepareStatement(
						QUERY_NAME);
				System.out.println("Last Name");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			else if(type.equals("Phone No"))
			{
				pstmt = conn.prepareStatement(
						QUERY_PHONE);
				System.out.println("Phone No");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			else
			{
				pstmt = conn.prepareStatement(
						QUERY_EMP_ID);
				System.out.println("Employee ID");
				pstmt.setString(1, input);
				rs = pstmt.executeQuery();
			}
			
			if(rs.next())
			{
				rs.first();
				String [] queryArray = new String [17];
				queryArray[0] = rs.getString("empId");
				queryArray[1] = rs.getString("empPassword");
				queryArray[2] = rs.getString("idNumber");
				queryArray[3] = rs.getString("surname");
				queryArray[4] = rs.getString("name");
				queryArray[5] = rs.getString("initials");
				queryArray[6] = rs.getString("title");
				queryArray[7] = rs.getString("address1");
				queryArray[8] = rs.getString("address2");
				queryArray[9] = rs.getString("address3");
				queryArray[10] = rs.getString("address4");
				queryArray[11] = rs.getString("phone1");
				queryArray[12] = rs.getString("phone2");
				queryArray[13] = rs.getString("email");
				queryArray[14] = rs.getString("birthDate");
				queryArray[15] = rs.getString("joinDate");
				queryArray[16] = rs.getString("accessLevel");
				if(queryArray[16].equals("1"))
				{
					queryArray[16] = "Assistant";
				}
				if(queryArray[16].equals("2"))
				{
					queryArray[16] = "Stock Controller";
				}
				if(queryArray[16].equals("3"))
				{
					queryArray[16] = "Manager";
				}
				
				//Sends through a String array which the Employee class
				//uses to initialise all its attributes.
				Employee e = new Employee(queryArray);
				return e;
			}
			else
			{
				return null;
			}
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
			return null;
		}
	}
	//This method updates an employee record on the DB, which allows
	//an end user to edit the details of an employee.
	public void update(Employee emp)
	{
		try
		{
			this.emp = emp;
			String [] str = emp.getEmpAttrString();
			PreparedStatement ps = conn.prepareStatement(
					UPDATE_EMP);
			if(str[16].equals("Assistant"))
			{
				str[16] = "1";
			}
			if(str[16].equals("Stock Controller"))
			{
				str[16] = "2";
			}
			if(str[16].equals("Manager"))
			{
				str[16] = "3";
			}
			ps.setString(1, str[0]);
			ps.setString(2, str[1]);
			ps.setString(3, str[2]);
			ps.setString(4, str[3]);
			ps.setString(5, str[4]);
			ps.setString(6, str[5]);
			ps.setString(7, str[6]);
			ps.setString(8, str[7]);
			ps.setString(9, str[8]);
			ps.setString(10, str[9]);
			ps.setString(11, str[10]);
			ps.setString(12, str[11]);
			ps.setString(13, str[12]);
			ps.setString(14, str[13]);
			ps.setString(15, str[14]);
			ps.setString(16, str[15]);
			ps.setString(17, str[16]);
			ps.setString(18, str[0]);
			System.out.println(str[0]);
		
			int rowsChanged = ps.executeUpdate();
			System.out.println(rowsChanged);
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
	}
	//This generates an employee ID with the first three letters
	//of an employee's surname followed by a number beginning at 1001.
	//Thus if there were two employees with the last name of Smith,
	//the first would have an ID of smi1001 and the second of smi1002.
	//This is achieved by extracting all existing IDs from the DB
	//and comparing the generated ID against them to ensure that
	//no duplicates are possible.
	public String generateEmpId(String surname)
	{
		try
		{
			String lastName = surname;
			lastName = lastName.toLowerCase();
			Statement stmt = conn.createStatement(
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			ResultSet rs;
			String query = "SELECT empId FROM employeeTable";
			rs = stmt.executeQuery(query);
			
			//Outer loop, which increments the suffix from 
			//1001 to a max of 11000.
			outer: for(int i = 0; i < 10000; i++)
			{
				String surSub = lastName.substring(0,3);
				int suffixInt = 1001+i;
				String suffix = "" + suffixInt;
				System.out.println(surSub);
				
				String subSurSub = surSub + suffix;
				System.out.println(subSurSub);
				
				inner://Inner loop, which checks for duplicates
				while(rs.next())
				{
					if(subSurSub.equals(rs.getString("empId")))
					{
						System.out.println("it's equal");
						continue outer;
					}
				}
				System.out.println("It's not");
				rs.close();
				return subSurSub;
			}
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
		return "Error";
	}
	//This method generates a unique password for each employee,
	//which they'll use (independently of their ID) to log on for any
	//functions. In order to generate the password, each of four
	//positions in a string are given a random number from 1-10.
	//In practice, this means that approximately 10 000 unique passwords
	//are possible, while an inner loop ensures that there are no duplicates.
	public String generateEmpPassword()
	{
		try
		{
			Statement stmt = conn.createStatement(
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
			ResultSet rs;
			String query = "SELECT empPassword FROM employeeTable";
			rs = stmt.executeQuery(query);
			outer: while(true)
			{
				int [] nums = new int []{1,2,3,4,5,6,7,8,9,0};
				int pos1, pos2, pos3, pos4;
				pos1 = ((int)(Math.random()*10.0));
				pos2 = ((int)(Math.random()*10.0));
				pos3 = ((int)(Math.random()*10.0));
				pos4 = ((int)(Math.random()*10.0));
				
				stringPass = "" + pos1 + pos2 + pos3 + pos4;
				System.out.println(stringPass);
				PasswordHasher passwordHasher = new PasswordHasher("SHA-256");
	      		byte[] passwordBytes = stringPass.getBytes();
	      		String passwordHash = passwordHasher.hashPassword(passwordBytes);
				inner:
				while(rs.next())
				{
					if(passwordHash.equals(rs.getString("empPassword")))
					{
						System.out.println("it's equal");
						continue outer;
					}
				}
				System.out.println("It's not");
				rs.close();
				return passwordHash;
			}
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();	
		}
		catch(NoSuchAlgorithmException nsae)
		{
			nsae.printStackTrace();
		}
		return "";
	}
	public String getEmpPlainTextPassword()
	{
		return stringPass;
	}
	public String [] getInfoPanel(String empId)
	{
		String [] output = new String[7];
		try
		{
			String days30 = "SELECT count(empId) from eventTable WHERE "+
			 "eventType = 'Loan' and eventDate BETWEEN "+
			"(current_Date() - 30) AND current_Date() and empId = '"+
			empId+"'";
			String loanCount = "SELECT count(empId) from eventtable "+
			"where empId like '"+empId+"' and eventType like 'Loan'";
			String days30Sales = "SELECT SUM(eventValue) FROM eventTable "+
			"WHERE eventType = 'Loan' AND empId = '"+empId+"' AND "+
			"eventDate BETWEEN (current_Date()-30) AND current_Date()";
			String joinDate = "SELECT joinDate from employeeTable "+
			"WHERE empId like '"+empId+"'";
			String age = "SELECT (YEAR(CURDATE())-YEAR(birthDate)) "+
			"- (RIGHT(CURDATE(),5)<RIGHT(birthDate,5)) AS age FROM"+
			" employeeTable WHERE empId = '"+empId+"'";
			String refunds30 = "SELECT count(empId) from eventTable WHERE "+
			 "eventType = 'Refund' and eventDate BETWEEN "+
			"(current_Date() - 30) AND current_Date() AND empId = '"+
			empId+"'";
			String avg30 = "SELECT count(empId) from eventTable WHERE "+
			 "eventType = 'Loan' and eventDate BETWEEN "+
			"(current_Date() - 30) AND current_Date() and empId = '"+
			empId+"'";
			String eventId;
			
			ResultSet rs;
			Statement stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(age);
			if(rs.next())
			{
				output[0] = rs.getString("age");
			}
			else
			{
				output[0] = "";
			}
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(days30);
			if(rs.next())
			{
				output[1] = ""+ rs.getInt(1);
			}
			else
			{
				output[1] = "";
			}
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(loanCount);
			if(rs.next())
			{
				output[2] = "" + rs.getInt(1);
			}
			else
			{
				output[2] = "";
			}
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(days30Sales);
			if(rs.next())
			{
				output[3] = ""+ rs.getDouble(1);
			}
			else
			{
				output[3] = "";
			}
			
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(refunds30);
			if(rs.next())
			{
				output[4] = ""+ rs.getInt(1);
			}
			else
			{
				output[4] = "";
			}
			
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(joinDate);
			if(rs.next())
			{
				output[5] = rs.getDate(1).toString();
			}
			else
			{
				output[5] = "";
			}
			
			stmt = conn.createStatement(
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(avg30);
			if(rs.next())
			{
				output[6] = ""+rs.getInt(1);
			}
			else
			{
				output[6] = "";
			}
			
		}
		catch(SQLException sqlx)
		{
			sqlx.printStackTrace();
		}
		return output;
		//SideInfoPanel sp = new SideInfoPanel(conn, output);
	}
}